import json
from openpyxl import load_workbook
import fitz  # PyMuPDF
import re
import json
import requests
import logging
from typing import Optional, Dict, Any
import pandas as pd
import os
import glob
import pytesseract
from pdf2image import convert_from_path
import os

import fitz  # PyMuPDF
import pytesseract
from PIL import Image
import io
import pandas as pd


def extract_packing_list_info(file_path):
    # 读取 Excel 文件中的 Packing List 工作表
    df_packing = pd.read_excel(file_path, sheet_name="Packing List", header=None)

    # 创建一个字典来存储提取的信息
    info = {
            # "MBL/HBL No.": df_packing.iloc[1, 1],   有些文件无法获取
            "Invoice No.": df_packing.iloc[1, 4],
            "Container No.": df_packing.iloc[2, 1],
            # "Date of Exportation": df_packing.iloc[2, 4],
            "Shipper/Exporter Company": df_packing.iloc[4, 1],
            "Shipper/Exporter Address": df_packing.iloc[5, 1],
            "Consignee Company": df_packing.iloc[4, 4],
            "Consignee Address": df_packing.iloc[5, 4]
            }

    # 提取基本信息（通过标签定位）
    # 找到第一个所有列都为空的行的索引
    df = df_packing
    for ii in range(len(df)):
        if df.iloc[ii].isna().all() or (df.iloc[ii].astype(str).str.strip() == '').all():
            first_empty_idx = ii
            break
    else:
        first_empty_idx = len(df)

    # 删除从开始到第一个空行的所有行
    df_cleaned = df.iloc[first_empty_idx + 1:].reset_index(drop=True)

    # 查找总计行（更通用的方法）
    for i in range(len(df_cleaned)):
        row = df_cleaned.iloc[i]
        # 检查是否包含 "Total" 或 "总计" 等关键词
        if any(keyword in str(row).lower() for keyword in ['total', '总计', '合计']):
            # 查找包含数值的列
            for col in range(len(row)):
                cell_value = row.iloc[col]
                # 检查前一个单元格的标题
                if col > 0:
                    prev_header = str(df_cleaned.iloc[0, col]).lower() if i > 0 else ""
                    current_value = row.iloc[col]

                    # 根据标题识别不同类型的总计
                    if 'box' in prev_header or 'ctns' in prev_header:
                        if pd.notna(current_value) and str(current_value).replace('.', '').isdigit():
                            info["Total Box Qty"] = current_value
                    elif 'g.w' in prev_header or 'gross' in prev_header or 'weight' in prev_header:
                        if pd.notna(current_value) and (str(current_value).replace('.', '').isdigit() or
                                                        (isinstance(current_value,
                                                                    (int, float)) and current_value > 0)):
                            info["Total G.W."] = current_value
                    elif 'cbm' in prev_header or 'volume' in prev_header:
                        if pd.notna(current_value) and (str(current_value).replace('.', '').isdigit() or
                                                        (isinstance(current_value,
                                                                    (int, float)) and current_value > 0)):
                            info["Total CBM"] = current_value

    return info




# 备用方法：直接通过位置提取（如果通用方法不适用）
def extract_packing_list_info_direct(file_path):
    return extract_packing_list_info(file_path)


def parse_excel_with_openpyxl(file_path):
    """
    使用openpyxl解析Excel文件
    """
    try:
        # 加载工作簿
        workbook = load_workbook(file_path)
        sheet = workbook['Commercial Invoice']

        extracted_data = {
            'MBL/HBL No.': sheet['B2'].value or "",
            'Container No.': sheet['B3'].value or "",
            'Invoice No.': "",  # 需要特殊处理
            'Date of Exportation': str(sheet['G3'].value) if sheet['G3'].value else "",
            'Shipper/Exporter Company': str(sheet['B5'].value).strip() if sheet['B5'].value else "",
            'Shipper/Exporter Address': str(sheet['B6'].value).strip() if sheet['B6'].value else "",
            'Consignee Company': str(sheet['G5'].value).strip() if sheet['G5'].value else "",
            'Consignee Address': str(sheet['G6'].value).strip() if sheet['G6'].value else ""
        }

        # 查找Invoice No.
        for row in sheet.iter_rows(max_row=10):  # 只在前10行搜索
            for cell in row:
                if cell.value and 'Invoice No.' in str(cell.value):
                    # 获取右侧相邻单元格的值作为发票号
                    next_cell = sheet.cell(row=cell.row, column=cell.column + 1)
                    extracted_data['Invoice No.'] = str(next_cell.value) if next_cell.value else ""
                    break

        return extracted_data

    except Exception as e:
        return {"error": f"解析文件时出错: {str(e)}"}


def clean_pdf_text(text: str) -> str:
    """清理PDF文本中的特殊空格字符"""
    # 替换各种空格字符
    space_patterns = [
        '\u00A0',  # NBSP
        '\u2000',  # 半身空距
        '\u2001',  # 全身空距
        '\u2002',  # 半身空距
        '\u2003',  # 全身空距
        '\u2004',  # 三分空距
        '\u2005',  # 四分空距
        '\u2006',  # 六分空距
        '\u2007',  # 数字空距
        '\u2008',  # 标点空距
        '\u2009',  # 细空距
        '\u200A',  # 最细空距
        '\u202F',  # 窄NBSP
        '\u205F',  # 中等数学空格
        '\u3000',  # 全角空格
    ]

    for pattern in space_patterns:
        text = text.replace(pattern, ' ')

    # 使用正则表达式进一步清理
    text = clean_str(text)
    return text.strip()

def _extract_pdf( pdf_path: str) -> str:
    """使用ChatGLM提取数据"""
    full_text = ""
    try:
        doc = fitz.open(pdf_path)
        for page in doc:
            full_text += page.get_text()
        doc.close()
    except Exception as e:
        print(f"cause exception:{e}")
        pass
    if len(full_text.strip()) == 0:
        full_text = extract_pdf_ocr(pdf_path)
    return full_text



def extract_pdf_ocr(pdf_path):
    """使用OCR提取图片PDF内容"""
    try:
        print("开始将PDF转换为图片...")

        # 将PDF转换为图片
        images = convert_from_path(pdf_path, dpi=300)  # 提高DPI获得更好识别效果

        print(f"成功转换 {len(images)} 页")

        full_text = ""
        for i, image in enumerate(images):
            print(f"正在OCR识别第 {i + 1} 页...")

            # 使用pytesseract进行OCR，支持中英文
            text = pytesseract.image_to_string(image, lang='chi_sim+eng')
            full_text += f"===== Page {i + 1} =====\n"
            full_text += text + "\n\n"

            # 可选：保存图片用于调试
            image.save(f"page_{i + 1}.jpg", "JPEG")

        return full_text

    except Exception as e:
        print(f"OCR提取失败: {e}")
        return None


def smart_extract_pdf(pdf_path, output_folder="extracted_images"):
    """
    智能提取PDF内容：文本直接提取，图片使用OCR
    """
    # 创建输出文件夹
    # if not os.path.exists(output_folder):
    #     os.makedirs(output_folder)

    doc = fitz.open(pdf_path)
    full_content = ""
    image_count = 0

    for page_num in range(len(doc)):
        page = doc[page_num]
        page_content = f"\n===== Page {page_num + 1} =====\n"

        # 1. 首先尝试直接提取文本
        text_content = page.get_text()

        if text_content.strip():
            page_content += "【直接提取的文本】\n"
            page_content += text_content + "\n"
        else:
            page_content += "【未找到直接文本，使用OCR】\n"

        # 2. 提取并处理图片
        image_list = page.get_images()

        if image_list:
            page_content += f"【本页包含 {len(image_list)} 张图片】\n"

            for img_index, img in enumerate(image_list):
                try:
                    # 获取图片
                    xref = img[0]
                    pix = fitz.Pixmap(doc, xref)

                    if pix.n - pix.alpha < 4:  # 检查是否是RGB
                        # 转换为PIL Image
                        img_data = pix.tobytes("ppm")
                        pil_image = Image.open(io.BytesIO(img_data))

                        # 保存图片
                        # img_filename = f"page_{page_num + 1}_img_{img_index + 1}.png"
                        # img_path = os.path.join(output_folder, img_filename)
                        # pil_image.save(img_path)

                        # 对图片进行OCR
                        ocr_text = pytesseract.image_to_string(pil_image, lang='chi_sim+eng')

                        page_content += f"【图片 {img_index + 1} OCR结果】\n"
                        page_content += ocr_text + "\n"

                        image_count += 1

                    pix = None  # 释放内存

                except Exception as e:
                    page_content += f"【图片 {img_index + 1} 处理失败: {str(e)}】\n"

        # 3. 如果直接文本很少但页面内容很多，可能整个页面都是图片
        if len(text_content.strip()) < 50 and page.rect.width > 0:
            page_content += "【检测到可能为扫描页面，进行整体OCR】\n"

            # 将整个页面转换为图片进行OCR
            mat = fitz.Matrix(2, 2)  # 提高分辨率
            pix = page.get_pixmap(matrix=mat)
            img_data = pix.tobytes("ppm")
            pil_image = Image.open(io.BytesIO(img_data))

            full_page_ocr = pytesseract.image_to_string(pil_image, lang='chi_sim+eng')
            page_content += full_page_ocr + "\n"

        full_content += page_content

    doc.close()

    print(f"处理完成！")
    print(f"- 总页数: {len(doc)}")
    print(f"- 提取图片数: {image_count}")
    print(f"- 图片保存到: {output_folder}")

    return full_content


def get_files(dir_path, match="BL-*.pdf"):
    """
    获取目录下所有以'BL-'开头的PDF文件

    Args:
        dir_path (str): 目录路径
        match (str): 匹配文件名

    Returns:
        list: 根据return_type参数返回相应的文件信息
    """
    if not os.path.isdir(dir_path):
        raise ValueError(f"目录不存在: {dir_path}")

    # 使用glob查找文件
    pattern = os.path.join(dir_path, match)
    return glob.glob(pattern)


def obtain_data(dir_path):
    pdf_file = get_files(dir_path,match="BL-*.pdf")
    excel_file = get_files(dir_path,match="*Commercial*.xlsx")
    if len(pdf_file) == 0 or len(excel_file) == 0:
        print(f"目录下找不到匹配文件，目录:{dir_path},pdf:{len(pdf_file)},excel:{len(excel_file)}")
        return None
    excel_data = extract_packing_list_info(excel_file[0])
    pdf_data = _extract_pdf(pdf_file[0])
    if len(pdf_data) == 0 :
        return None
    checkExcelDataByPdf(excel_data, pdf_data, pdf_file)
    save_data = {
        "text": pdf_data,
        "labels": excel_data,
        # "pdf": pdf_file[0],
        # "excel": excel_file[0],
    }
    return save_data


def checkExcelDataByPdf(excel_data, pdf_data, pdf_file):
    pdf_text_clean = clean_pdf_text(pdf_data)
    for key, value in excel_data.items():
        value_clean = clean_str(value)
        if len(value_clean.strip()) == 0:
            print(f"key:{key},value is empty,pdf:{pdf_file}")
            continue
        find = pdf_text_clean.find(value_clean)
        if find < 0 and key.find("Invoice") < 0:
            if value_clean.isdigit():
                ff = float(value_clean)
                f_str = f'{ff:,}'
                if pdf_text_clean.find(f_str) < 0:
                    print(f"{key}: {value},未找到{pdf_file[0]}\n")


def clean_str(value):
    value_clean = str(value).strip().replace('\n', ' ')
    value_clean = re.sub(r'\s+', ' ', value_clean)  # 合并多个空格
    value_clean = re.sub(r'\s*:\s*', ':', value_clean)  # 合并多个空格
    value_clean = re.sub(r'，', ',', value_clean)  # 合并多个空格
    value_clean = re.sub(r'\s*,\s*', ',', value_clean)  # 合并多个空格

    return value_clean


if __name__ == '__main__':
        # 使用示例
    # file_path = "GAOU6252103--Commercial Invoice&Packing List.xlsx"
    # result = parse_excel_with_openpyxl(file_path)
    # json_output = json.dumps(result, indent=2, ensure_ascii=False)
    # print(json_output)
    path = r'D:\存档\存档-已导入'
    all_data = []
    counter = 0
    for item  in os.listdir(path):
        # print(f"当前目录: {root}")
        print(f"子目录: {item}")
        # print(f"文件: {files}")
        print("-" * 50)
        sub = f'{path}/{item}'
        if not os.path.isdir(sub):
            continue
        data = obtain_data(sub)
        if data is None:
            continue
        counter = counter + 1
        all_data.append(data)
    print(f"获取数据{counter}条")
    save_path = r'D:\存档\存档-已导入\dataset_info.jsonl'
    # with open(save_path, 'w', encoding='utf-8') as f:
    #     json.dump(all_data, f, ensure_ascii=False, indent=2)
    if os.path.exists(save_path):
        os.remove(save_path)

    with open(save_path, 'w', encoding='utf-8') as f:
        for item in all_data:
            json.dump(item, f, ensure_ascii=False)
            f.write('\n')  # 每行一个JSON对象